﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Criterion;
using OurSite.Business.Picture;

namespace OurSite.Business.Repositories
{
    public class PictureRepository : BaseRepository, IPictureRepository
    {
        public void Add(Picture.Picture image)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Save(image);
                transaction.Commit();
            }
        }

        public void Update(Picture.Picture image)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Update(image);
                transaction.Commit();
            }
        }

        public void Remove(Picture.Picture image)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.Delete(image);
                transaction.Commit();
            }
        }

        public Picture.Picture GetById(Guid pictureId)
        {
            using (var tx = session.BeginTransaction())
            {
                var picture = session.Get<Picture.Picture>(pictureId);
                tx.Commit();
                return picture;
            }
        }
        
        public Picture.Picture GetListingImageByEventID(Guid eventId)
        {
            using (var tx = session.BeginTransaction())
            {
                Picture.Picture img = session
                    .CreateCriteria<Picture.Picture>()
                    .Add(Restrictions.Eq("EventId", eventId))
                    .UniqueResult<Picture.Picture>();
                tx.Commit();
                return img;
            }
        }

        public Picture.Picture GetBlankPicture()
        {
            Picture.Picture picture = new Picture.Picture{ ImagePath = ""};
            return picture;
        }

        public IList<Picture.Picture> GetByEventID(Guid eventId)
        {
            using (var tx = session.BeginTransaction())
            {
                var imgs = session
                    .CreateCriteria<Picture.Picture>()
                    .Add(Restrictions.Eq("EventId", eventId))
                    .AddOrder(Order.Asc("Order"))
                    .List<Picture.Picture>();

                tx.Commit();
                return imgs;
            }
        }

        public IList<Picture.Picture> GetAllPictures()
        {
            using (var tx = session.BeginTransaction())
            {
                var imgs = session
                    .CreateCriteria<Picture.Picture>()
                    .AddOrder(Order.Asc("Order"))
                    .List<Picture.Picture>();

                tx.Commit();
                return imgs;
            }

        }

        public IList<Picture.Picture> GetAllFavoritePictures()
        {
            using (var tx = session.BeginTransaction())
            {
                var imgs = session
                    .CreateCriteria<Picture.Picture>()
                    .Add(Restrictions.Eq("IsFavorite", true))
                    .List<Picture.Picture>();

                tx.Commit();
                return imgs;
            }
        }

        public IList<Picture.Picture> GetTopFavoritePictures(int numberToReturn)
        {
            using (var tx = session.BeginTransaction())
            {
                var imgs = session
                    .CreateCriteria<Picture.Picture>()
                    .Add(Restrictions.Eq("IsFavorite", true))
                    .SetMaxResults(numberToReturn)
                    .AddOrder(new RandomOrder())
                    .List<Picture.Picture>();

                tx.Commit();
                return imgs;
            }
        }

        public List<Picture.Picture> Search(PictureQuery query)
        {
            var pictures = GetAllPictures();
            var results = new List<Picture.Picture>();

            if (!string.IsNullOrEmpty(query.Caption))
                results = (from p in pictures where p.Caption.ToLower().Contains(query.Caption.ToLower()) select p).ToList();

            return results;
        }

        #region Get Listing image
        public Picture.Picture GetRegionListingPicture(string regionName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE Event_Region = '" + regionName + "' and IsRegionListingImage = 1")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.UniqueResult<Picture.Picture>();

                tx.Commit();
                return list;
            }

        }

        public Picture.Picture GetStateListingPicture(string stateName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE Event_State = '" + stateName + "' and IsStateListingImage = 1")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.UniqueResult<Picture.Picture>();

                tx.Commit();
                return list;
            }

        }

        public Picture.Picture GetDecadeListingPicture(string startYear, string endYear)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE DatePart(year, Event_StartDate) between " + startYear + " and " + endYear + " and IsDecadeListingImage = 1")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.UniqueResult<Picture.Picture>();

                tx.Commit();
                return list;
            }
        }

        public Picture.Picture GetPeopleListingPicture(PeopleCategory peopleCategory)
        {
            StringBuilder sb = new StringBuilder();
            switch (peopleCategory)
            {
                case PeopleCategory.IsHardenListingImage:
                    sb.Append("IsHardenListingImage = 1");
                    break;
                case PeopleCategory.IsRyanListingImage:
                    sb.Append("IsRyanListingImage = 1");
                    break;
                case PeopleCategory.IsFriendListingImage:
                    sb.Append("IsFriendListingImage = 1");
                    break;
                default:
                    break;
            }

            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM PicturePeople pp INNER JOIN People pl ON pp.People_ID = pl.People_ID INNER JOIN Picture {p} ON pp.Picture_ID = p.Picture_ID WHERE " + sb)
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.UniqueResult<Picture.Picture>();

                tx.Commit();
                return list;
            }
        }

        public Picture.Picture GetCategoryListingPicture(string categoryName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Picture {p} INNER JOIN Category c ON p.Picture_ID = c.CategoryListingImage WHERE c.Category_Name = '" + categoryName + "'")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.UniqueResult<Picture.Picture>();

                tx.Commit();
                return list;
            }
        }
        #endregion

        #region Set Lising Image
        public void SetCategoryListingImage(Guid pictureId, Guid categoryId)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                Object result = session.CreateSQLQuery("UPDATE Category SET CategoryListingImage = '" + pictureId + "' WHERE Category_ID = '" + categoryId + "'")
                    .UniqueResult();

                transaction.Commit();
            }
        }

        public void SetRegionListingImage(Guid pictureId, string regionName)
        {
            ResetRegionListingImage(regionName);
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsRegionListingImage = 1 WHERE Picture_ID = '" + pictureId + "'")
                    .UniqueResult();

                transaction.Commit();
            }
        }

        public void SetStateListingImage(Guid pictureId, string stateName)
        {
            ResetStateListingImage(stateName);
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsStateListingImage = 1 WHERE Picture_ID = '" + pictureId + "'")
                    .UniqueResult();

                transaction.Commit();
            }
        }

        public void SetDecadeListingImage(Guid pictureId, string startYear, string endYear)
        {
            ResetDecadeListingImage(startYear, endYear);
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsDecadeListingImage = 1 WHERE Picture_ID = '" + pictureId + "'")
                    .UniqueResult();

                transaction.Commit();
            }
        }
        #endregion

        #region Get All possible Lising Images

        public IList<Picture.Picture> GetCategoryAllListingPicture(string categoryName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Category c INNER JOIN Category cp ON cp.Parent_ID = c.Category_ID INNER JOIN EventCategory ec ON cp.Category_ID = ec.Category_ID INNER JOIN Picture {p} ON ec.Event_ID = p.Event_ID WHERE c.Category_Name = '" + categoryName + "'")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.List<Picture.Picture>();

                tx.Commit();
                return list;
            }

        }
        
        public IList<Picture.Picture> GetRegionAllListingPicture(string regionName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE Event_Region = '" + regionName + "'")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.List<Picture.Picture>();

                tx.Commit();
                return list;
            }

        }

        public IList<Picture.Picture> GetStateAllListingPicture(string stateName)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE Event_State = '" + stateName + "'")
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.List<Picture.Picture>();

                tx.Commit();
                return list;
            }
        }

        public IList<Picture.Picture> GetDecadeAllListingPicture(string startYear, string endYear)
        {
            using (var tx = session.BeginTransaction())
            {
                IQuery query = session.CreateSQLQuery("SELECT {p.*} FROM Event e INNER JOIN Picture {p} ON e.Event_ID = p.Event_ID WHERE DatePart(year, Event_StartDate) between " + startYear + " and " + endYear)
                    .AddEntity("p", typeof (Picture.Picture));
                var list = query.List<Picture.Picture>();

                tx.Commit();
                return list;
            }
        }
        #endregion

        public int GetNextPictureCode()
        {
            using (var tx = session.BeginTransaction())
            {
                int pictureCode = session
                    .CreateCriteria<Picture.Picture>()
                    .SetProjection(
                    Projections.Max(
                        Projections.Cast(NHibernateUtil.Int32, Projections.Property("PictureCode"))
                        )
                    )
                    .UniqueResult<int>();

                tx.Commit();
                return pictureCode + 1;
            }
        }

        public int GetNextOrderByEventID(Guid eventId)
        {
            using (var tx = session.BeginTransaction())
            {

                int order = session
                    .CreateCriteria<Picture.Picture>()
                    .Add(Restrictions.Eq("EventId", eventId))
                    .SetProjection(
                    Projections.Max(
                        Projections.Cast(NHibernateUtil.Int32, Projections.Property("Order"))
                        )
                    )
                    .UniqueResult<int>();

                tx.Commit();
                return order + 1;
            }
        }

        #region Protected Methods
        protected void ResetRegionListingImage(string regionName)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsRegionListingImage = 0 FROM Event e INNER JOIN Picture p ON e.Event_ID = p.Event_ID WHERE Event_Region = '" + regionName + "' and IsRegionListingImage = 1")
                    .UniqueResult();

                session.Transaction.Commit();
            }

        }

        protected void ResetStateListingImage(string stateName)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsStateListingImage = 0 FROM Event e INNER JOIN Picture p ON e.Event_ID = p.Event_ID WHERE Event_State = '" + stateName + "' and IsStateListingImage = 1")
                    .UniqueResult();

                session.Transaction.Commit();
            }
        }

        protected void ResetDecadeListingImage(string startYear, string endYear)
        {
            using (ITransaction transaction = session.BeginTransaction())
            {
                session.CreateSQLQuery("UPDATE Picture SET IsDecadeListingImage = 0 FROM Event e INNER JOIN Picture p ON e.Event_ID = p.Event_ID WHERE DatePart(year, Event_StartDate) between " + startYear + " and " + endYear + " and IsDecadeListingImage = 1")
                    .UniqueResult();

                session.Transaction.Commit();
            }

        }
        #endregion
    }
}
